Query to check active users in EBS

Many times, it so happens that all that you are looking for in google is a query for your problem. I will be putting across important queries in the coming few days, that will be handy in our day to day lives while working on Oracle E-Business Suite.

Today we will look into queries to get the number of active users connected to the application and all the active users connect to the (EBS)application.

Tested In: Oracle EBS 12.1.1, 12.1.3, 12.2.6

First lets get the all the active users connected to the application using the below query:

SELECT fnd.node_name
      ,(SELECT user_name 
          FROM fnd_user f 
         WHERE f.user_id = icx.user_id) user_name
  FROM icx_sessions icx
      ,fnd_nodes fnd
 WHERE icx.disabled_flag  <> 'Y'
   AND icx.PSEUDO_FLAG     = 'N'
   AND icx.node_id         = fnd.node_id
   AND (icx.last_connect +
       decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
               NULL,
               icx.limit_time,
               0,
               icx.limit_time,
               FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) > SYSDATE       
   AND counter              < icx.limit_connects 

This query below, will help you to get the number of all active users connected to the application:

SELECT icx.node_id,
       fnd.node_name,
       count(distinct icx.session_id) "ACTIVE_USER_SESSIONS_COUNT"
  FROM icx_sessions icx
      ,fnd_nodes fnd
 WHERE icx.disabled_flag != 'Y'
   AND icx.PSEUDO_FLAG    = 'N'
   AND icx.node_id        = fnd.node_id
   AND (icx.last_connect +
       decode(FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT'),
               NULL,
               icx.limit_time,
               0,
               icx.limit_time,
               FND_PROFILE.VALUE('ICX_SESSION_TIMEOUT') / 60) / 24) >  SYSDATE
   AND counter             < icx.limit_connects
 GROUP by icx.node_id, fnd.node_name 

Hope this helps.